# 该脚本用于统计某一工具一段时间内的生产数量，可多次执行，
# 每次执行如果发现上一次执行的pid仍然存在，则会先终止进程，然后继续运行。
import os
import signal
import time
import math
import json
import threading as thd
import psutil
from sqlalchemy import create_engine

__author__ = 'tangzk@yeah.net'

pids_dir = '../../pids/'
pid_file_name = 'cstnc.pid'
pid_path = pids_dir + pid_file_name

if not os.path.exists(pids_dir):
    os.makedirs(pids_dir, mode=0o777)

SHOW_SQL = False

oracle_engine = None
mysql_engine = None


def connect():
    global oracle_engine
    global mysql_engine
    database_uri = 'oracle://ALS_RC:w4client@dbalsf72.bmwbrill.cn:1540/dbalsf72'
    oracle_engine = create_engine(database_uri, echo=SHOW_SQL)
    database_uri = 'mysql+pymysql://app:123qaz@160.46.82.135:3306/bbf41'
    mysql_engine = create_engine(database_uri, echo=SHOW_SQL)


def disconnect():
    oracle_engine.dispose()
    mysql_engine.dispose()


def unique_handler():
    # 判断进程文件夹是否存在。
    # 获取上一次运行该脚本的进程，并判断该进程是否存在，如果存在则退出当前脚本
    if os.path.exists(pid_path):
        fh = open(pid_path, 'r')
        last_pid = int(fh.read())
        fh.close()
        try:
            pso = psutil.Process(last_pid)
            pso_name = pso.name()
            if pso_name.find('python') == 0:
                print('进程存在，正在终止进程...')
                os.kill(last_pid, signal.SIGTERM)
        except psutil.NoSuchProcess:
            print('进程不存在，继续运行...')
            pass


def record_pid():
    pid = os.getpid()
    fh = open(pid_path, 'w')
    fh.write(str(pid))
    fh.close()


def get_foundry_coreshop_tool_number():
    lp_dict = {}
    sql = "SELECT ANLAGEN_KZ,WERKZEUGE from anlagenstatus where anlagen_kz like 'CS%' AND ANLAGEN_KZ NOT LIKE'CSE%'"
    rows = oracle_engine.execute(sql)
    for row in rows:
        akz = row['anlagen_kz']
        tool_info = row['werkzeuge']
        tool_vals = tool_info.split(',')
        rtns = tool_vals[18]
        tool_number = tool_vals[19]
        if int(tool_number) > 0:
            lp_dict[akz] = {'tool_number': tool_number, 'rtns': rtns}
    return lp_dict


# 从数据库里查询当前产量
def query_production_data():
    from_time_str = time.strftime('%Y-%m-%d 06:00:00', time.localtime(time.time()))
    to_time_str = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    sql = "SELECT AKZ, RTNS, COUNT(*) CNT FROM KERNSCHUSSDATEN WHERE AKZ LIKE 'CS%' AND " \
          "ZEITPUNKT >= TO_DATE('" + from_time_str + "', 'yyyy-mm-dd hh24:mi:ss') AND " \
                                                     "ZEITPUNKT  < TO_DATE('" + to_time_str + "', 'yyyy-mm-dd hh24:mi:ss') GROUP BY AKZ, RTNS"
    return oracle_engine.execute(sql)


def build_tool_info_dict():
    rows = []
    _dict = {}
    akz_rtns_dict = {}
    production_data = query_production_data()
    for parts_item in production_data:
        tmp_obj = {}
        for column, value in parts_item.items():
            tmp_obj[column] = value
        rows.append(tmp_obj)
    tool_number_dict = get_foundry_coreshop_tool_number()
    for row in rows:
        akz = row['akz']  # 设备名，如CS11
        rtns = row['rtns']
        akz_rtns = akz + '_' + rtns
        cnt = int(row['cnt'])
        if akz_rtns not in akz_rtns_dict:
            akz_rtns_dict[akz_rtns] = {}
            akz_rtns_dict[akz_rtns]['value'] = 0
            akz_rtns_dict[akz_rtns]['value'] += cnt
        if akz in tool_number_dict:
            item = tool_number_dict[akz]
            tool_number = item['tool_number']
            tool_idx = akz_rtns + '_' + tool_number
            if tool_idx not in _dict:
                _dict[tool_idx] = {}
                _dict[tool_idx]['value'] = 0
            _dict[tool_idx]['value'] += cnt
            _dict[tool_idx]['akz'] = akz
            _dict[tool_idx]['rtns'] = rtns
            _dict[tool_idx]['tool_number'] = tool_number
    return _dict


# step 1 从Mysql里，根据设备名、统计标记，查询是否有记录。
# 如果没有记录，没有记录，则产量差值为0。
# 如果有记录，如果产量数值相同，则不增加新记录，如果产量不相同，则将当前的产量与上一条记录的产量值相减，将差值记录到表中，
# 当差值为负值时，再去判断当前时间与上一条记录的时间差，差值在一天以内的话为正常，产量差值为当前的产值，当差值在一天以上的话，有可能是
# 因为服务器有一段时间脚本没有运行，些时也直接用当前产值做为差值。所以这里就不去对时间做判断了。


# 从Mysql里获取最近一条记录
def get_last_record(akz, rtns):
    _sql = 'SELECT * FROM coreshop_tool_number_counter WHERE flag=0 AND akz="%s" AND rtns="%s" ORDER BY id DESC LIMIT 1' % (
        akz, rtns)
    result_proxy = mysql_engine.execute(_sql)
    return result_proxy.first()


# 批量更新
def batch_update_counter():
    date_number = int(time.strftime('%Y%m%d'))
    current_timestamp = int(time.time())
    tool_info_dict = build_tool_info_dict()
    updated_counter = 0
    if tool_info_dict:
        for (key, obj) in tool_info_dict.items():
            _is_updated = False
            akz = obj['akz']
            rtns = obj['rtns']
            tool_number = int(obj['tool_number'])
            row = get_last_record(akz, rtns)
            current_production = obj['value']
            # 如果存在上一条记录，则将当前产量与上一条记录对比，如果前者减后者为正数，该值为两者时间段内产量的增值
            # 否则，将当前产量值做为增量记录到数据库。当本次的akz与上一条记录的akz对比，如果相同，按正常流程走，否
            # 则，应该通过akz来获取上一条记录。
            if row:
                last_production = row['production_value']
                difference = current_production - last_production
                if difference != 0:
                    _is_updated = True
                    if difference < 0:
                        if int(row['tool_number']) == tool_number:
                            difference = current_production
                        else:
                            difference = 0
            else:
                _is_updated = True
                difference = 0

            if _is_updated:
                updated_counter += 1
                row_tuple = (rtns, tool_number, akz, obj['value'], difference, date_number, current_timestamp, 0)
                record_increment(row_tuple)
    return updated_counter


# 记录增量
def record_increment(row_tuple):
    _sql = 'INSERT INTO coreshop_tool_number_counter (rtns, tool_number,akz,production_value,difference,date_number,' \
           'update_timestamp,flag) VALUES ("%s", %d,"%s",%d,%d,%d,%d,%d)' % row_tuple
    return mysql_engine.execute(_sql)


# 获取flag=0的数据总条数
def get_count_for_counter():
    _sql = 'SELECT COUNT(*) cnt FROM coreshop_tool_number_counter WHERE flag=0 LIMIT 1'
    result_proxy = mysql_engine.execute(_sql)
    row = result_proxy.fetchone()
    return row['cnt']


def query_counter_list(page_number, size):
    offset = page_number * size
    _sql = 'SELECT rtns, tool_number,akz,difference FROM coreshop_tool_number_counter WHERE flag=0 LIMIT %d, %d' % (
        offset, size)
    return mysql_engine.execute(_sql)


def get_last_record_by_akz_and_tool_number(akz, tool_number):
    _sql = 'SELECT * FROM coreshop_tool_number_counter WHERE flag=0 AND akz="%s" AND tool_number="%s" ORDER BY update_timestamp DESC LIMIT 1' % (
    akz, tool_number)
    result_proxy = mysql_engine.execute(_sql)
    return result_proxy.fetchone()


def recount_to_mysql(counter_storage):
    for (k, val) in counter_storage.items():
        pos = k.index('_')
        akz = k[0:pos]
        tool_number = k[pos + 1:]
        record = get_last_record_by_akz_and_tool_number(akz, tool_number)
        _sql = 'UPDATE coreshop_tool_number_counter SET flag=2 WHERE flag=0 AND akz="%s" AND tool_number="%s"' % (
            akz, tool_number)
        mysql_engine.execute(_sql)
        recount_record_tuple = (record['rtns'], record['tool_number'], record['akz'],
                                record['production_value'], val, record['date_number'], record['update_timestamp'], 0)
        record_increment(recount_record_tuple)


# 更新计数器
def update_counter():
    counter_storage = {}
    cnt = get_count_for_counter()
    if cnt > 0:
        size = 200  # 每次处理的数据条数
        page_number = math.ceil(cnt / size)
        for i in range(0, page_number):
            result_proxy = query_counter_list(i, size)
            if result_proxy.rowcount > 0:
                for row in result_proxy:
                    akz = row['akz']
                    tool_number = row['tool_number']
                    idx = akz + '_' + str(tool_number)
                    if idx not in counter_storage:
                        counter_storage[idx] = 0
                    counter_storage[idx] += row['difference']
        if page_number > 10:
            recount_to_mysql(counter_storage)
    cache_counter_result(counter_storage)


# 缓存计算结果
def cache_counter_result(counter_storage):
    file_path = '../../../app/static/data/FoundryCoreshopToolNumberCounter.json'
    fh = open(file_path, 'w')
    content = json.dumps(counter_storage)
    fh.write(content)
    fh.close()
    print(content)


def loop():
    record_pid()
    connect()
    is_updated = batch_update_counter()
    if is_updated:
        update_counter()
    disconnect()
    thd.Timer(10, loop).start()


if __name__ == '__main__':
    unique_handler()
    loop()
